using Exhort.Entity.Basic;
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;

namespace Exhort.DbSet.Basic
{
    public class BasicMenuDbSet : TreeDbSet<BasicMenu>
    {
        public List<BasicMenu> EffectiveAll()
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT M.ID");
            sql.AppendLine(",M.InsertTime");
            sql.AppendLine(",M.UpdateTime");
            sql.AppendLine(",M.Path");
            sql.AppendLine(",M.Pid");
            sql.AppendLine(",M.Sequence");
            sql.AppendLine(",M.Title");
            sql.AppendLine(",M.Icon");
            sql.AppendLine(",M.Type");
            sql.AppendLine(",M.Module");
            sql.AppendLine(",M.Value");
            sql.AppendLine(",M.Src");
            sql.AppendLine(",M.IsEnabled");
            sql.AppendLine("FROM Basic_Menu AS M");
            sql.AppendLine("INNER JOIN Basic_MenuFunction AS MF ON MF.MenuID = M.ID");
            sql.AppendLine("INNER JOIN Basic_Function AS F ON F.ID = MF.FunctionID");
            sql.AppendLine("WHERE M.IsEnabled = 1");
            sql.AppendLine("AND F.Type = 'power'");
            sql.AppendLine("AND F.Value = 'view'");
            sql.AppendLine("ORDER BY M.Sequence ASC");

            return dbContext.Dapper.Query<BasicMenu>(sql.ToString()).ToList();
        }

        public List<BasicMenu> EffectiveByUserId(string userId)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT DISTINCT M.ID");
            sql.AppendLine(",M.InsertTime");
            sql.AppendLine(",M.UpdateTime");
            sql.AppendLine(",M.Path");
            sql.AppendLine(",M.Pid");
            sql.AppendLine(",M.Sequence");
            sql.AppendLine(",M.Title");
            sql.AppendLine(",M.Icon");
            sql.AppendLine(",M.Type");
            sql.AppendLine(",M.Module");
            sql.AppendLine(",M.Value");
            sql.AppendLine(",M.Src");
            sql.AppendLine(",M.IsEnabled");
            sql.AppendLine("FROM Basic_Menu AS M");
            sql.AppendLine("INNER JOIN Basic_MenuFunction AS MF ON MF.MenuID = M.ID");
            sql.AppendLine("INNER JOIN Basic_Function AS F ON F.ID = MF.FunctionID");
            sql.AppendLine("INNER JOIN Basic_RoleMenuFunction AS RMF ON RMF.MenuFunctionID = MF.ID");
            sql.AppendLine("INNER JOIN Basic_UserRole AS UR ON UR.RoleID = RMF.RoleID");
            sql.AppendLine("INNER JOIN Basic_Role AS R ON R.ID = UR.RoleID");
            sql.AppendLine("WHERE M.IsEnabled = 1");
            sql.AppendLine("AND F.Type = 'power'");
            sql.AppendLine("AND F.Value = 'view'");
            sql.AppendLine("AND UR.UserID = @UserID");
            sql.AppendLine("AND R.IsEnabled = 1");
            sql.AppendLine("ORDER BY M.Sequence ASC");

            return dbContext.Dapper.Query<BasicMenu>(sql.ToString(), new { UserID = userId }).ToList();
        }
    }
}